package dao.impl;

import dao.AdminDao;
import pojo.Admin;
import pojo.Menu;
import pojo.Role;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 后台用户接口的实现类
 * @author 叶军军
 */
public class AdminDaoImpl implements AdminDao {

    /**
     * 后台登录功能
     */
    @Override
    public Admin login(String account, String password) {
        Admin admin=new Admin();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn= BaseDao.getConnection();
            String sql="select * from admin WHERE ad_account=? and ad_password=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,account);
            ps.setString(2,password);
            rs=ps.executeQuery();
            while(rs.next()){
                admin.setAdId(rs.getInt("ad_id"));
                admin.setAdImg(rs.getString("ad_img"));
                admin.setAdAccount(rs.getString("ad_account"));
                admin.setAdPassword(rs.getString("ad_password"));
                admin.setAdName(rs.getString("ad_name"));
                admin.setAdSex(rs.getInt("ad_sex"));
                admin.setAdPhone(rs.getString("ad_phone"));
                admin.setAdEmail(rs.getString("ad_email"));
                admin.setAdDesc(rs.getString("ad_desc"));
                admin.setAdTime(rs.getTimestamp("ad_time"));
                admin.setAdState(rs.getInt("ad_state"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return admin;
    }

    /**
     * 根据ID查询管理员信息
     */
    @Override
    public Admin queryAdmin(int id) {
        Admin admin=new Admin();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn= BaseDao.getConnection();
            String sql="select * from admin WHERE ad_id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while(rs.next()){
                admin.setAdId(rs.getInt("ad_id"));
                admin.setAdImg(rs.getString("ad_img"));
                admin.setAdAccount(rs.getString("ad_account"));
                admin.setAdPassword(rs.getString("ad_password"));
                admin.setAdName(rs.getString("ad_name"));
                admin.setAdSex(rs.getInt("ad_sex"));
                admin.setAdPhone(rs.getString("ad_phone"));
                admin.setAdEmail(rs.getString("ad_email"));
                admin.setAdDesc(rs.getString("ad_desc"));
                admin.setAdTime(rs.getTimestamp("ad_time"));
                admin.setAdState(rs.getInt("ad_state"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return admin;
    }

    /**
     *根据管理员id查询管理员角色对象
     */
    @Override
    public Role queryOneRole(int id) {
        Role role=new Role();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String sql="select distinct ro.* from adross ur,role ro,admin ad " +
                    "where ro.ro_id=ur.role_id and ur.admin_id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while(rs.next()){
                role.setRoId(rs.getInt("ro_id"));
                role.setRoName(rs.getString("ro_name"));
                role.setRoDesc(rs.getString("ro_desc"));
                role.setRoRool(rs.getString("ro_rool"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return role;
    }

    /**
     *根据用户ID查询菜单
     */
    @Override
    public List<Menu> roolMenu(int id) {
        List<Menu> menus=new ArrayList<Menu>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String sql="Select distinct m.* from menu m ,admin u, adross ur, romess rm  " +
                    "Where m.me_rool=1 and m.me_id = rm.menu_id and ur.role_id = rm.role_id and ur.admin_id =?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while (rs.next()){
                Menu menu=new Menu();
                menu.setMeId(rs.getInt("me_id"));
                menu.setMeName(rs.getString("me_name"));
                menu.setMeUrl(rs.getString("me_url"));
                menu.setMeHigh(rs.getInt("me_high"));
                menus.add(menu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return menus;
    }

    /**
     * 分页功能
     */
    @Override
    public PageBean adminPageList(PageBean pb, String ename, String account) {
        List<Admin> list=new ArrayList<Admin>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String s="";
            if(null!=ename && !ename.equals("")){
                s+=" and  ad_Name LIKE '%"+ename+"%'";
            }
            String u="";
            if(null!=account && !account.equals("")){
                u+=" and ad_Account like '%"+account+"%'";
            }
            String sql=" SELECT * FROM (" +
                    "  SELECT ROWNUM r,admin.* FROM admin " +
                    "  WHERE ROWNUM<=?" + s
                    +" )WHERE r>?"+u;
            ps=conn.prepareStatement(sql);
            //给占位符号进行赋值
            ps.setInt(1,pb.getPageIndex()*pb.getPageSize());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs=ps.executeQuery();
            while(rs.next()){
                Admin admin=new Admin();
                admin.setAdId(rs.getInt("ad_id"));
                admin.setAdImg(rs.getString("ad_img"));
                admin.setAdAccount(rs.getString("ad_account"));
                admin.setAdPassword(rs.getString("ad_password"));
                admin.setAdName(rs.getString("ad_name"));
                admin.setAdSex(rs.getInt("ad_sex"));
                admin.setAdPhone(rs.getString("ad_phone"));
                admin.setAdEmail(rs.getString("ad_email"));
                admin.setAdDesc(rs.getString("ad_desc"));
                admin.setAdTime(rs.getTimestamp("ad_time"));
                admin.setAdState(rs.getInt("ad_state"));
                list.add(admin);
            }
            pb.setList(list);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return pb;
    }

    /**
     * 统计总数
     */
    @Override
    public int count(String ename, String account) {
        int count=0;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String s="";
            if(null!=ename && !ename.equals("")){
                s+="  and  ad_Name LIKE '%"+ename+"%' ";
            }
            String u="";
            if(null!=account && !account.equals("")){
                u+=" and ad_Account like '%"+account+"%'";
            }

            String sql="select count(*) from admin  where 1=1 "+s+u;
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 新增管理员
     */
    @Override
    public int inseAdmin(Admin admin) {
        return BaseDao.cud("insert into admin values(sequence_admin.nextval,?,?,?,?,?,?,?,?,?,?)",admin.getAdImg(),admin.getAdAccount(),admin.getAdPassword(),admin.getAdName(),admin.getAdSex(),admin.getAdPhone(),admin.getAdEmail(),admin.getAdDesc(),admin.getAdTime(),admin.getAdState());
    }

    /**
     *根据管理员账户查找管理员id
     */
    @Override
    public int queryAdIdByAccount(String account) {
        int id=0;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=BaseDao.getConnection();
            String sql="select * from admin WHERE ad_account=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,account);
            rs=ps.executeQuery();
            while(rs.next()){
                id=rs.getInt("ad_id");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs,ps,conn);
        }
        return id;
    }

    /**
     *修改管理员
     */
    @Override
    public int modifAdmin(Admin admin) {
        return BaseDao.cud("update admin set ad_account=?,ad_name=?,ad_sex=?,ad_phone=?,ad_email=?,ad_time=?,ad_desc=?,ad_state=? where ad_id=?",admin.getAdAccount(),admin.getAdName(),admin.getAdSex(),admin.getAdPhone(),admin.getAdEmail(),admin.getAdTime(),admin.getAdDesc(),admin.getAdState(),admin.getAdId());
    }

    /**
     *删除管理员
     */
    @Override
    public int deleAdmin(int id) {
        return BaseDao.cud("delete from admin where ad_id=?",id);
    }

    /**
     * 修改管理员密码
     */
    @Override
    public int modifPassword(String password,int id) {
        return BaseDao.cud("update admin set ad_password=? where ad_id=?",password,id);
    }
}
